package jam.sql;

import java.sql.*;
import java.util.*;

import javax.sql.rowset.CachedRowSet;

public class SQLController {
	private static SQLController singletonInstance;
	private Connection con;

	private SQLController() {
		try {
			Class.forName("org.sqlite.JDBC");
			con = DriverManager.getConnection("jdbc:sqlite:./data/jam.db");
		} catch (Exception e) {
			System.err.println("could not initialise database: " + e.getMessage());
		}
	}

	public static SQLController getInstance() {
		if (SQLController.singletonInstance == null)
			SQLController.singletonInstance = new SQLController();
		return SQLController.singletonInstance;
	}

	public boolean insert(String tableName, HashMap<String,String> insertFields) throws SQLException {
		String fieldList = "";
		String valueList = "";
		String delimiter = "";
		for(Map.Entry entry: insertFields.entrySet()) {
			fieldList += delimiter + entry.getKey();
			valueList += delimiter + "'" + entry.getValue() + "'";
			delimiter = ",";
		}
		String statement = "insert into " + tableName + " (" + fieldList + ") VALUES (" + valueList + ");";
		Statement stat = con.createStatement();
		stat.executeUpdate(statement);
		return true;
	}
	
	public boolean update(String tableName, int index, HashMap<String,String> updateFields) throws SQLException {
		String setList = "";
		for(Map.Entry entry: updateFields.entrySet()) {
			setList += entry.getKey() + "=" + "'" + entry.getValue() + "'" + ",";
		}
		setList = setList.substring(0,setList.length()-1);
		String statement = "update " + tableName + " SET " + setList + " WHERE id = " + index + ";";
		Statement stat = con.createStatement();
		stat.executeUpdate(statement);
		return true;
	}

	public boolean update(ResultSet row) {
		CachedRowSet rowSet = (CachedRowSet) row;
		try {
			rowSet.updateRow();
			rowSet.acceptChanges(con);
			return true;
		} catch (Exception e) {
			return false;
		}
	}

	public CachedRowSet get(String tableName, Map<String, String> populateFields, String[] returnFields) throws SQLException {
		String searchStr ="";
		String delimiter = "";
		for(Map.Entry entry: populateFields.entrySet()) {
			searchStr += delimiter + entry.getKey() + "=\"" + entry.getValue() + "\"";
			delimiter = " and ";
		}
		String returnVals = "";
		delimiter = "";
		if(returnFields == null) {
			returnVals = "*";
		} else {
			for(String returnField: returnFields) {
				returnVals += delimiter + returnField;
				delimiter = ",";
			}
		}
		Statement stat = con.createStatement();
		String statementStr = "select " + returnVals + " from " + tableName + " WHERE " + searchStr + ";";
		ResultSet results = stat.executeQuery(statementStr);
		CachedRowSet cachedResults = CachedRowSetFactory.getCachedRowSet();
		cachedResults.populate(results);
		results.close();
		return cachedResults;
	}
}

